package com.nit.lab.mapper.ship;

import com.nit.lab.dto.ship.ShipDTO;
import com.nit.lab.entity.ship.ShipEntity;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface ShipMapper {

    /**
     * @描述 获取未出港的船
     * @创建人 fyx
     * @创建时间 2021/2/20
     */
    @Select({"<script>SELECT * " +
            "FROM ship " +
            "WHERE 1 = 1  " +
            "</script>"})
    List<ShipDTO> queryShip() throws Exception;
    /**
     * @描述 获取数据表格
     * @创建人 zmh
     * @创建时间 2021/2/2
     */
    @Select({"<script>SELECT *," +
            "ship_id AS `key`, ship_id, " +
            "ship_name, country_id, type_id, " +
            "berthing_time, departure_time, " +
            "begin_port_id, target_port_id, " +
            "category_id, risk_grade, " +
            "grade_id, declare_time, " +
            "epidemic_type_id, " +
            "remark_any, gmt_create, " +
            "mmsi_number, is_deleted " +
            "FROM ship " +
            "WHERE 1 = 1 AND is_deleted = 0 " +
            "<if test='portStatusId != null'> AND port_status_id = #{portStatusId} </if>" +
            "<if test='shipName != null'> AND ship_name LIKE CONCAT('%', #{shipName}, '%') </if>" +
            "<if test='mmsiNumber != null'> AND mmsi_number LIKE CONCAT('%', #{mmsiNumber}, '%') </if>" +
            "<if test='countryId != null'> AND country_id = #{countryId} </if>" +
            "<if test='typeId != null'> AND type_id = #{typeId} </if>" +
            "<if test='beginPortId != null'> AND begin_port_id = #{beginPortId} </if>" +
            "<if test='targetPortId != null'> AND target_port_id = #{targetPortId} </if>" +
            "<if test='gradeId != null'> AND grade_id = #{gradeId} </if>" +
            "<if test='categoryId != null'> AND category_id = #{categoryId} </if>" +
            "<if test='epidemicTypeId != null'> AND epidemic_type_id = #{epidemicTypeId} </if>" +
            "ORDER BY " +
            "<if test='sortField != null'>" +
            "${sortField} ${sortOrder}, " +
            "</if>" +
            "ship_id desc " +
            "LIMIT #{start}, #{length}" +
            "</script>"})
    List<ShipDTO> list4Table(
           @Param("start") Integer start,
           @Param("length") Integer length,
           @Param("portStatusId") String portStatusId,
           @Param("shipName") String shipName,
           @Param("mmsiNumber") String mmsiNumber,
           @Param("countryId") String countryId,
           @Param("typeId") String typeId, //船舶类型
           @Param("beginPortId") String beginPortId,
           @Param("targetPortId") String targetPortId,
           @Param("gradeId") String gradeId,
           @Param("categoryId") String categoryId,
           @Param("epidemicTypeId") String epidemicTypeId, //涉疫类型
           @Param("sortField") String sortField,
           @Param("sortOrder") String sortOrder) throws Exception;


    /**
     * @描述 获取删除船舶记录
     * @创建人 zmh
     * @创建时间 2021/2/2
     */
    @Select({"<script>SELECT " +
            "ship_id AS `key`, ship_id, " +
            "ship_name, country_id, type_id, " +
            "berthing_time, departure_time, " +
            "begin_port_id, target_port_id, " +
            "category_id, risk_grade, " +
            "grade_id, declare_time, " +
            "epidemic_type_id, " +
            "remark_any, gmt_create, " +
            "mmsi_number, is_deleted " +
            "FROM ship " +
            "WHERE 1 = 1 AND is_deleted = 0 " +
            "<if test='portStatusId != null'> AND port_status_id LIKE CONCAT('%', #{portStatusId}, '%') </if>" +
            "<if test='shipName != null'> AND ship_name LIKE CONCAT('%', #{shipName}, '%') </if>" +
            "<if test='mmsiNumber != null'> AND mmsi_number LIKE CONCAT('%', #{mmsiNumber}, '%') </if>" +
            "<if test='countryId != null'> AND country_id = #{countryId} </if>" +
            "<if test='typeId != null'> AND type_id = #{typeId} </if>" +
            "<if test='beginPortId != null'> AND begin_port_id = #{beginPortId} </if>" +
            "<if test='targetPortId != null'> AND target_port_id = #{targetPortId} </if>" +
            "<if test='gradeId != null'> AND grade_id = #{gradeId} </if>" +
            "<if test='categoryId != null'> AND category_id = #{categoryId} </if>" +
            "<if test='epidemicTypeId != null'> AND epidemic_type_id = #{epidemicTypeId} </if>" +
            "ORDER BY " +
            "<if test='sortField != null'>" +
            "${sortField} ${sortOrder}, " +
            "</if>" +
            "ship_id desc " +
            "LIMIT #{start}, #{length}" +
            "</script>"})
    List<ShipDTO> list4LeaveTable(
            @Param("start") Integer start,
            @Param("length") Integer length,
            @Param("portStatusId") String portStatusId,
            @Param("shipName") String shipName,
            @Param("mmsiNumber") String mmsiNumber,
            @Param("countryId") String countryId,
            @Param("typeId") String typeId, //船舶类型
            @Param("beginPortId") String beginPortId,
            @Param("targetPortId") String targetPortId,
            @Param("gradeId") String gradeId,
            @Param("categoryId") String categoryId,
            @Param("epidemicTypeId") String epidemicTypeId, //涉疫类型
            @Param("sortField") String sortField,
            @Param("sortOrder") String sortOrder) throws Exception;



    /**
     * @Description: 获取表格数据记录的总条数
     * @Author: zmh
     * @Date: 2021/2/2
     */
    @Select({"<script>SELECT COUNT(*) " +
            "FROM ship " +
            "WHERE 1 = 1 AND is_deleted = 0 " +
            "<if test='shipName != null'> AND ship_name LIKE CONCAT('%', #{shipName}, '%') </if>" +
            "<if test='mmsiNumber != null'> AND mmsi_number LIKE CONCAT('%', #{mmsiNumber}, '%') </if>" +
            "<if test='countryId != null'> AND country_id = #{countryId} </if>" +
            "<if test='typeId != null'> AND type_id = #{typeId} </if>" +
            "<if test='beginPortId != null'> AND begin_port_id = #{beginPortId} </if>" +
            "<if test='targetPortId != null'> AND target_port_id = #{targetPortId} </if>" +
            "<if test='gradeId != null'> AND grade_id = #{gradeId} </if>" +
            "</script>"})
    Long count4Table(
            @Param("shipName") String shipName,
            @Param("mmsiNumber") String mmsiNumber,
            @Param("countryId") String countryId,
            @Param("typeId") String typeId,
            @Param("beginPortId") String beginPortId,
            @Param("targetPortId") String targetPortId,
            @Param("gradeId") String gradeId) throws Exception;


    /**
     * @Description: 新增记录
     * @Author: SN
     * @Date: 2019/01/30 18:15
     */
    @Insert("INSERT INTO ship (" +
            "ship_name, country_id, " +
            "type_id, " +
            "berthing_time, " +
            "begin_port_id, target_port_id, category_id, risk_grade, grade_id, " +
            "declare_time, remark_any, epidemic_type_id, gmt_create, mmsi_number, port_status_id ) " +
            "VALUES (#{shipEntity.shipName}, #{shipEntity.countryId}, " +
            "#{shipEntity.typeId}, " +
            "#{shipEntity.berthingTime}, " +
            "#{shipEntity.beginPortId}, #{shipEntity.targetPortId}, #{shipEntity.categoryId}, #{shipEntity.riskGrade}, " +
            "#{shipEntity.gradeId}, #{shipEntity.declareTime}, #{shipEntity.remarkAny}, #{shipEntity.epidemicTypeId}, " +
            "#{shipEntity.gmtCreate}, #{shipEntity.mmsiNumber}, 1)")
    @Options(useGeneratedKeys = true, keyProperty = "shipId", keyColumn = "ship_id")
    Integer add(@Param("shipEntity") ShipEntity shipEntity) throws Exception;

    /**
     * @Description: 更新记录
     * @Author: zmg
     * @Date: 2020/02/02
     */
    @Update("UPDATE ship " +
            "SET ship_name = #{shipEntity.shipName}, " +
            "country_id = #{shipEntity.countryId}, " +
            "type_id = #{shipEntity.typeId}, " +
            "berthing_time = #{shipEntity.berthingTime}, " +
            "departure_time = #{shipEntity.departureTime}, " +
            "begin_port_id = #{shipEntity.beginPortId}, " +
            "target_port_id = #{shipEntity.targetPortId}, " +
            "category_id = #{shipEntity.categoryId}, " +
            "risk_grade = #{shipEntity.riskGrade}, " +
            "grade_id = #{shipEntity.gradeId}, " +
            "declare_time = #{shipEntity.declareTime}, " +
            "remark_any = #{shipEntity.remarkAny}, " +
            "gmt_create = #{shipEntity.gmtCreate}, " +
            "gmt_modified = #{shipEntity.gmtModified}, " +
            "mmsi_number = #{shipEntity.mmsiNumber} " +
            "WHERE ship_id = #{shipEntity.shipId}")
    Integer update(@Param("shipEntity") ShipEntity shipEntity) throws Exception;

    /**
     * @Description: 增加船舶离港记录
     * @Author: zmh
     * @Date: 2020/02/05
     */
    @Update("UPDATE ship " +
            "SET port_status_id = 2, " +
            "departure_time = #{shipEntity.departureTime} " +
            "WHERE ship_id = #{shipEntity.shipId}")
    Integer leavePort(@Param("shipEntity") ShipEntity shipEntity) throws  Exception;


    /**
     * @Description: 删除记录
     * @Author: zmh
     * @Date: 2020/02/02
     */
    @Update("UPDATE ship SET is_deleted = 1 WHERE ship_id = #{shipId}")
    Integer remove(@Param("shipId") Long shipId) throws Exception;

    /**
     * @Description: 根据shipID获取用户信息
     * @Author: SN
     * @Date: 2019/01/30 18:15
     */
    @Select({"SELECT " +
            "ship_id, ship_name, country_id, " +
            "type_id, " +
            "berthing_time, " +
            "departure_time, " +
            "begin_port_id, target_port_id, category_id, risk_grade, grade_id, " +
            "declare_time, remark_any, gmt_create, gmt_modified, mmsi_number " +
            "FROM ship " +
            "WHERE ship_id = #{shipId} AND is_deleted = 0 "
    })
    ShipDTO getByShipId(@Param("shipId") Long shipId) throws Exception;

    /**
     * @Description: 根据mmsiNumber名称获取用户信息
     * @Author: ZMH
     * @Date: 2019/01/30 18:15
     */
    @Select({"SELECT " +
            "ship_id, ship_name, country_id, " +
            "type_id, " +
            "berthing_time, " +
            "departure_time, " +
            "begin_port_id, target_port_id, category_id, risk_grade, grade_id, " +
            "declare_time, remark_any, gmt_create, gmt_modified, mmsi_number " +
            "FROM ship " +
            "WHERE mmsi_number = #{mmsiNumber} AND is_deleted = 0 LIMIT 1"
    })
    ShipDTO getByMmsiNumber(@Param("mmsiNumber") String mmsiNumber) throws Exception;





}
